const { pool } = require('../config/database');

class User {
  // 查找所有用户
  static async findAll() {
    try {
      const [rows] = await pool.execute(
        'SELECT id, name, email, phone, age, status, created_at, updated_at FROM users ORDER BY created_at DESC'
      );
      return rows;
    } catch (error) {
      console.error('查询所有用户失败:', error.message);
      throw error;
    }
  }
  
  // 根据ID查找用户
  static async findById(id) {
    try {
      const [rows] = await pool.execute(
        'SELECT id, name, email, phone, age, status, created_at, updated_at FROM users WHERE id = ?',
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      console.error('根据ID查询用户失败:', error.message);
      throw error;
    }
  }
  
  // 根据邮箱查找用户
  static async findByEmail(email) {
    try {
      const [rows] = await pool.execute(
        'SELECT id, name, email, phone, age, status, created_at, updated_at FROM users WHERE email = ?',
        [email]
      );
      return rows[0] || null;
    } catch (error) {
      console.error('根据邮箱查询用户失败:', error.message);
      throw error;
    }
  }
  
  // 创建用户
  static async create({ name, email, phone = null, age = null }) {
    try {
      const [result] = await pool.execute(
        'INSERT INTO users (name, email, phone, age, status) VALUES (?, ?, ?, ?, ?)',
        [name, email, phone, age, 'active']
      );
      return result.insertId;
    } catch (error) {
      console.error('创建用户失败:', error.message);
      throw error;
    }
  }
  
  // 更新用户
  static async update(id, { name, email, phone, age, status }) {
    try {
      const [result] = await pool.execute(
        'UPDATE users SET name = ?, email = ?, phone = ?, age = ?, status = ? WHERE id = ?',
        [name, email, phone, age, status || 'active', id]
      );
      return result.affectedRows > 0;
    } catch (error) {
      console.error('更新用户失败:', error.message);
      throw error;
    }
  }
  
  // 删除用户
  static async delete(id) {
    try {
      const [result] = await pool.execute('DELETE FROM users WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除用户失败:', error.message);
      throw error;
    }
  }
  
  // 获取用户总数
  static async count() {
    try {
      const [rows] = await pool.execute('SELECT COUNT(*) as total FROM users');
      return rows[0].total;
    } catch (error) {
      console.error('获取用户总数失败:', error.message);
      throw error;
    }
  }
  
  // 根据状态查找用户
  static async findByStatus(status = 'active') {
    try {
      const [rows] = await pool.execute(
        'SELECT id, name, email, phone, age, status, created_at, updated_at FROM users WHERE status = ? ORDER BY created_at DESC',
        [status]
      );
      return rows;
    } catch (error) {
      console.error('根据状态查询用户失败:', error.message);
      throw error;
    }
  }
}

module.exports = User;
